package com.gxuwz.medical.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.menu.Menu;
import com.gxuwz.medical.vo.PageBean;

public class MenuDao {
	public void add(Menu m) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "insert into menu(menuid,menuName,menupid,url) values('"
					+ m.getMenuid() + "','" + m.getMenuname() + "','" + m.getMenupid() + "','" + m.getUrl() + "')";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("添加权限失败 " + e.getMessage(), e);
		}
	}

	public void delete(String menuid) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "delete from menu where menuid='" + menuid + "'";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("删除权限失败" + e.getMessage(), e);
		}
	}

	public Menu query(String menuid) throws SQLException {
		Menu m = new Menu();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from menu where menuid='" + menuid
					+ "'";
			rs = db.executeQuery(stmt, sql);
			if (rs.next()) {
				m.setMenuid(rs.getString("menuid"));
				m.setMenuname(rs.getString("menuName"));
				m.setMenupid(rs.getString("menupid"));
				m.setUrl(rs.getString("url"));
			}

		} catch (SQLException e) {
			throw new SQLException("查询权限失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return m;
	}
	
	public int count() throws SQLException{//返回记录数
		int count = 0;
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select count(menuid) from menu";
			rs = stmt.executeQuery(sql);
			if(rs.next())
				count = rs.getInt(1);
		}catch (SQLException e) {
			throw new SQLException("查询记录数量失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return count;
	}

	public PageBean queryAll(int page,int size) throws SQLException {
		PageBean p = new PageBean(page,size,count());
		List mlist = new ArrayList();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from menu limit "+(page-1)*size+","+size+"";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				Menu menu = new Menu();
				menu.setMenuid(rs.getString("menuid"));
				menu.setMenuname(rs.getString("menuName"));
				if(rs.getString("menupid")!=null)
					menu.setMenupid(rs.getString("menupid"));
				if(rs.getString("url")!=null)
					menu.setUrl(rs.getString("url"));
				mlist.add(menu);
			}
			p.setPageData(mlist);
			return p;

		} catch (SQLException e) {
			throw new SQLException("查询全部权限失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}
	
	public List<Menu> queryAll() throws SQLException {
		List<Menu> mlist = new ArrayList<Menu>();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from menu";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				Menu menu = new Menu();
				menu.setMenuid(rs.getString("menuid"));
				menu.setMenuname(rs.getString("menuName"));
				if(rs.getString("menupid")!=null)
					menu.setMenupid(rs.getString("menupid"));
				if(rs.getString("url")!=null)
					menu.setUrl(rs.getString("url"));
				mlist.add(menu);
			}
			
			return mlist;

		} catch (SQLException e) {
			throw new SQLException("查询全部权限失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}

	public void update(Menu m) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "update menu set menuName='" + m.getMenuname() + "',menupid='"+m.getMenupid()+"',url='"+m.getUrl()+"' where menuid='" + m.getMenuid() + "'";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("修改权限失败 " + e.getMessage(), e);
		}
	}
	
	public List<Menu> findChildren(String id)throws SQLException{
		List<Menu> children = new ArrayList<Menu>();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try{
		String sql = "select * from menu where menupid='"+id+"'";
		rs = stmt.executeQuery(sql);
		while(rs.next()){
			Menu m = new Menu();
			m.setMenuid(rs.getString("menuid"));
			m.setMenuname(rs.getString("menuName"));
			m.setMenupid(rs.getString("menupid"));
			m.setUrl(rs.getString("url"));
			children.add(m);
		}
		}catch (SQLException e) {
			throw new SQLException("查询子节点失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return children;
	}
	
	public boolean hasRole(Menu m,String roleid) throws SQLException{
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try{
			String sql = "select * from role_menu where roleid='"+roleid+"' and menuid='"+m.getMenuid()+"'";
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				return true;
			}
		}catch(SQLException e){
			
			throw new SQLException("查询子节点失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		
		return false;
	}
	
}
	
	

